﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EnterpriseDataAccess.Factory
{
    /// <summary>
    /// Clase que construye comandos SQL
    /// </summary>
    class SQLCommandBuilder
    {
        /// <summary>
        /// Genera un SELECT command apartir de un System.Data.DataTable.
        /// </summary>
        /// <param name="table">DataTable para generar la consulta.</param>
        /// <param name="useFilter">Indica si debe generar la clausula WHERE.</param>
        /// <returns>SELECT Command</returns>
        public static string SelectCommand(DataTable table, bool useFilter = false)
        {
            StringBuilder sql = new StringBuilder("SELECT ");
            StringBuilder values = new StringBuilder(" WHERE ");
            bool bFirst = true;

            foreach (DataColumn column in table.Columns)
            {
                    if (bFirst)
                    {
                        bFirst = false;
                    }
                    else
                    {
                        sql.Append(", ");
                    }

                    sql.Append(column.ColumnName);
            }

            sql.Append(" FROM " + Convert.ToString(table.TableName));

            if (useFilter)
            {
                bFirst = true;
                foreach (DataColumn c in table.PrimaryKey)
                {
                    if (bFirst)
                    {
                        bFirst = false;
                    }
                    else
                    {
                        values.Append(" AND ");
                    }
                    values.Append(c.ColumnName);
                    values.Append("=@");
                    values.Append(c.ColumnName);
                }

                sql.Append(values.ToString());

            }
            sql.Append(";");
            return sql.ToString();
        }

        /// <summary>
        /// Genera un INSERT command apartir de un System.Data.DataTable.
        /// </summary>
        /// <param name="table">DataTable para generar la consulta.</param>
        /// <returns>INSERT Command</returns>
        public static string InsertCommand(DataTable table)
        {
            StringBuilder sql = new StringBuilder("INSERT INTO " + Convert.ToString(table.TableName) + " (");
            StringBuilder values = new StringBuilder("VALUES (");
            bool bFirst = true;

            foreach (DataColumn column in table.Columns)
            {
                if (!column.AutoIncrement)
                {
                    if (bFirst)
                    {
                        bFirst = false;
                    }
                    else
                    {
                        sql.Append(", ");
                        values.Append(", ");
                    }

                    sql.Append(column.ColumnName);
                    values.Append("@");
                    values.Append(column.ColumnName);
                }
            }
            sql.Append(") ");
            sql.Append(values.ToString());
            sql.Append(");");

            return sql.ToString();
        }

        /// <summary>
        /// Genera un UPDATE command apartir de un System.Data.DataTable.
        /// </summary>
        /// <param name="table">DataTable para generar la consulta.</param>
        /// <returns>UPDATE Command</returns>
        public static string UpdateCommand(DataTable table)
        {
            StringBuilder sql = new StringBuilder("UPDATE " + Convert.ToString(table.TableName) + " SET ");
            StringBuilder values = new StringBuilder(" WHERE ");
            bool bFirst = true;

            foreach (DataColumn column in table.Columns)
            {
                if (!column.AutoIncrement)
                {
                    if (bFirst)
                    {
                        bFirst = false;
                    }
                    else
                    {
                        sql.Append(", ");
                    }

                    sql.Append(column.ColumnName);
                    sql.Append("=@");
                    sql.Append(column.ColumnName);
                }
            }
            bFirst = true;
            foreach (DataColumn c in table.PrimaryKey)
            {
                if (bFirst)
                {
                    bFirst = false;
                }
                else
                {
                    values.Append(" AND ");
                }
                values.Append(c.ColumnName);
                values.Append("=@");
                values.Append(c.ColumnName);
            }
            sql.Append(values.ToString());
            sql.Append(";");

            return sql.ToString();
        }

        /// <summary>
        /// Genera un DELETE command apartir de un System.Data.DataTable
        /// </summary>
        /// <param name="table">DataTable para generar la consulta.</param>
        /// <returns>DELETE Command</returns>
        public static string DeleteCommand(DataTable table)
        {
            StringBuilder sql = new StringBuilder("DELETE FROM " + Convert.ToString(table.TableName));
            StringBuilder values = new StringBuilder(" WHERE ");
            bool bFirst = true;

            foreach (DataColumn c in table.PrimaryKey)
            {
                if (bFirst)
                {
                    bFirst = false;
                }
                else
                {
                    values.Append(" AND ");
                }
                values.Append(c.ColumnName);
                values.Append("=@");
                values.Append(c.ColumnName);
            }
            sql.Append(values.ToString());
            sql.Append(";");

            return sql.ToString();
        }
    }
}
